'''

Case Type： 数据库系统
Case Name： 去除审计日志读权限
Create At:  2020/07/21
Owner    :  @li-xin12345
Descption:  测试配置pg_hba文件远程连接方式为md5后,普通用户连接是否成功
            1. 创建普通用户 2. 设置pg_hba.conf.conf文件中的METHOD为md5 3. 重启数据库使配置文件生效 4. 远程连接本机普通用户
            5. 用配置文件中配置的host连接本机普通用户 6. 删除新增配置，恢复配置文件 7. 删除新建普通用户
history：
created： 2020/07/21  created by @li-xin12345
'''

import os
import unittest
from yat.test import Node
import sys
from yat.test import macro
from testcase.utils.CommonSH import *

from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import *

logger = Logger()
sys.path.append(sys.path[0] + "/../")


class IndexFileDamaged(unittest.TestCase):
    sh_primy = CommonSH('PrimaryDbUser')
    sh_stadb = CommonSH('Standby1DbUser')
    nodes_tuple = ('PrimaryDbUser', 'Standby1DbUser', 'Standby2DbUser')

    @RestartDbCluster(*nodes_tuple)
    def setUp(self):
        logger.info('------------Opengauss_Reliability_Dbsys_Case218.py start------------')
        self.rootNode = Node('PrimaryRoot')
        self.primaryNode = Node(node='PrimaryDbUser')
        self.remoteNode = Node(node='remote_PrimaryDbUser')
        self.DB_ENV_PATH = macro.DB_ENV_PATH
        self.DB_INSTANCE_PATH = macro.DB_INSTANCE_PATH
        self.native_host = self.primaryNode.ssh_host
        self.remote_host = self.remoteNode.ssh_host
        self.REMOTE_ENV = macro.REMOTE_ENV
        self.native_db_name = self.primaryNode.db_name
        self.native_port = self.primaryNode.db_port
        self.native_name = self.primaryNode.ssh_user
        self.passwd_initial = macro.PASSWD_INITIAL
        self.passwd_replace = self.primaryNode.db_password
        self.Constant = Constant()

        msg = self.sh_primy.executeGsguc('reload', self.Constant.GSGUC_SUCCESS_MSG, 'password_encryption_type=2')
        self.assertTrue(msg)

    def test_Index_file_damaged(self):
        # 在本机创建普通用户
        logger.info('----------------------------在本机创建普通用户-----------------------------')
        sql_cmd = f'''
                                 drop user if exists jack ;
                                 create user jack identified by '{self.passwd_initial}';
                                 alter user jack identified by '{self.passwd_replace}' replace '{self.passwd_initial}';
                                 grant all privileges to jack;
                                 '''
        excute_cmd = f'''
                                 source {self.DB_ENV_PATH} ;
                                 gsql -d {self.primaryNode.db_name} -p {self.primaryNode.db_port} -c "{sql_cmd}"
                                 '''
        logger.info(excute_cmd)
        msg = self.primaryNode.sh(excute_cmd).result()
        logger.info(msg)
        self.assertIn(self.Constant.CREATE_ROLE, msg)

        # 改变远程连接方式
        logger.info('----------------------------更改配置文件-----------------------------')
        excute_cmd = f'''
                        source {self.DB_ENV_PATH};
                        gs_guc set -N all -I all -h "host    all    jack    {self.remote_host}/32    md5";
                    '''
        logger.info(excute_cmd)
        msg = self.primaryNode.sh(excute_cmd).result()
        logger.info(msg)
        self.assertIn(self.Constant.GSGUC_SUCCESS_MSG, msg)

        # 重启使参数生效
        logger.info('----------------------------重启数据库使参数生效-----------------------------')
        is_stopped = self.sh_primy.stopDbCluster()
        self.assertTrue(is_stopped)
        is_started = self.sh_primy.startDbCluster()
        self.assertTrue(is_started)

        # 重启使参数生效
        logger.info('----------------------------查看数据库状态-----------------------------')
        excute_cmd = f'''
                            source {self.REMOTE_ENV} ;
                            gs_om -t status --detail;

                            '''
        logger.info(excute_cmd)
        msg = self.remoteNode.sh(excute_cmd).result()
        logger.info(msg)
        # self.assertIn(self.Constant.START_SUCCESS_MSG, msg)

        # 用配置文件中配置的host连接本机普通用户
        logger.info('----------------------------用配置文件中配置的host连接本机普通用户-----------------------------')
        excute_cmd = f'''
                        source {self.REMOTE_ENV};
                        gsql -d {self.native_db_name} -h {self.native_host} -p {self.native_port} -U 'jack' -W '{self.passwd_replace}';
                        '''
        logger.info(excute_cmd)
        msg = self.remoteNode.sh(excute_cmd).result()
        logger.info(msg)
        self.assertIn(self.Constant.SQL_WRONG_MSG[3], msg)

        # 删除新建普通用户
        logger.info('----------------------------删除新建普通用户-----------------------------')
        sql_cmd = "drop user jack;"
        excute_cmd = f'''
                        source {self.DB_ENV_PATH};
                        gsql -d {self.primaryNode.db_name} -p {self.primaryNode.db_port} -c "{sql_cmd}";
                       '''
        logger.info(excute_cmd)
        msg = self.primaryNode.sh(excute_cmd).result()
        logger.info(msg)

    def tearDown(self):
        logger.info('----------------------------后置处理-----------------------------')
        try:
            db_status = self.sh_primy.getDbInstanceStatus()
            logger.info(db_status)
            if db_status:
                pass
            else:
                is_started = self.sh_primy.startDbCluster()
                self.assertTrue(is_started)
                logger.info(f'{is_started}')
                logger.info(f'db_status: {is_started}')
        finally:
            excute_cmd = f'''
                        source {self.DB_ENV_PATH};
                        gs_guc set -N all -I all -h "host    all    jack    {self.remote_host}/32";
                    '''
            logger.info(excute_cmd)
            msg = self.primaryNode.sh(excute_cmd).result()
            logger.info(msg)

            msg = self.sh_primy.executeGsguc('reload', self.Constant.GSGUC_SUCCESS_MSG, 'password_encryption_type=1')
            logger.info(msg)
        logger.info('----------------------------Reliability_Dbsys_Case218执行完成-----------------------------')
